﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Public;

namespace DAL
{
    public class SoHoKhauDAL
    {
        public static List<SoHoKhauPublic> LoadSoHoKhau()
        {
            var sohokhau = Common.db.ExecuteStoreQuery<SoHoKhauPublic>(@"select * from SoHoKhau");
            return sohokhau.ToList();
        }

        public static SoHoKhauPublic LoadSoHoKhauBySoSHK(int SoSHK)
        {
            string sql = @"select * from SoHoKhau where SoSoHoKhau=" + SoSHK;
            var sohokhau = Common.db.ExecuteStoreQuery<SoHoKhauPublic>(sql);
            //  if sohokhau == DBNull
            return sohokhau.FirstOrDefault();
        }
        public static List<SoHoKhauPublic> LoadListSoHoKhauBySoSHK(int SoSHK)
        {
            string sql = @"select * from SoHoKhau where SoSoHoKhau=" + SoSHK;
            var sohokhau = Common.db.ExecuteStoreQuery<SoHoKhauPublic>(sql);
            //  if sohokhau == DBNull
            return sohokhau.ToList();
        }
        public static void InsertSoHoKhau(SoHoKhauPublic _shk)
        {
            SoHoKhau shk = new SoHoKhau();
            shk.MaSHK = _shk.MaSHK;
            shk.SoSoHoKhau = _shk.SoSoHoKhau;
            shk.SoNha = _shk.SoNha;
            shk.Duong = _shk.Duong;
            shk.KhuPho = _shk.KhuPho;
            shk.CanBoDangKi = _shk.CanBoDangKi;
            shk.ChucVuNguoiCap = _shk.ChucVuNguoiCap;
            shk.NgayCap = _shk.NgayCap;
            shk.NguoiCap = _shk.NguoiCap;
            Common.db.AddToSoHoKhaus(shk);
            Common.db.SaveChanges();
        }
        public static int LoadMaSHK()
        {
            //var shk = Common.db.ExecuteStoreQuery<SoHoKhauPublic>(@"select Max(MaSHK) as MaSHK from SoHoKhau ");

            var shk = Common.db.ExecuteStoreQuery<int>(@"select ISNULL(Max(MaSHK),0) as MaSHK from SoHoKhau");
            return shk.ToList()[0];
        }
        public static SoHoKhauPublic LoadSoHoKhauByMaSHK(int MaSHK)
        {
            var shk = Common.db.ExecuteStoreQuery<SoHoKhauPublic>(@"select * from SoHoKhau where MaSHK=" + MaSHK);
            return shk.FirstOrDefault();
        }
        public static int CountSoHoKhau()
        {
            var shk = Common.db.ExecuteStoreQuery<int>(@"select count(MaSHK) from SoHoKhau");
            return shk.FirstOrDefault();
        }
        public static int LoadSoSoHoKhau()
        {
            var shk = Common.db.ExecuteStoreQuery<int>(@"select ISNULL(Max(SoSHK),0) as SoSHK from SoHoKhau");
            return shk.ToList()[0];
        }
        public static int CoutSoHoKhauByYear(int firstyear, int lastyear)
        {
            var shk = Common.db.ExecuteStoreQuery<int>(@"select count(MaSHK) from SoHoKhau where YEAR(NgayCap)>=" + firstyear + "and YEAR(NgayCap)<=" + lastyear);
            return shk.FirstOrDefault();
        }
        public static string LoadDiaChiSHKByMaNK(int MaNK)
        {
            var shk = Common.db.ExecuteStoreQuery<string>("select N'Số '+SoNha+N', Đường '+Duong+N', Khu Phố/Ấp '+CAST(KhuPho as nvarchar(2))  as DiaChi  from SoHoKhau where MaSHK in (select MaSHK from Nhankhau where MaNK=" + MaNK + ")");
            return shk.FirstOrDefault();
        }
        public static void UpdateSHK(string SoNha, int Ap, string Duong, int NguoiCap, int SoSoHoKhau, int ChucVu)
        {
            var shk = Common.db.ExecuteStoreQuery<SoHoKhauPublic>(@"update SoHoKhau set SoNha=N'" + SoNha + "',Duong=N'" + Duong + "',KhuPho=" + Ap + ",NguoiCap=" + NguoiCap + ",ChucVuNguoiCap=" + ChucVu + "where SoSoHoKhau=" + SoSoHoKhau);
            Common.db.SaveChanges();
        }
        public static void DeleteSHK(int SoSoHoKhau)
        {
            var shk = Common.db.ExecuteStoreQuery<NhanKhauPublic>("delete from SoHoKhau where SoSoHoKhau=" + SoSoHoKhau);
            Common.db.SaveChanges();
        }
        public static List<SoHoKhauPublic> BaoCaoSHK()
        {
            string chuoi = @"select t1.TenPhuongXa, SoSHK, NhanKhau, SoNu, Tuoi14
                            from
                            (
                            select right(px.MaPX,2) as c1, Count( distinct MaSHK) as SoSHK,COUNT(MaNK) as NhanKhau,TenPhuongXa
                            from Nhankhau,PhuongXa px, ThietLap where left(right('0'+cast(MaNK as varchar(8)),8),2)in(select RIGHT(MaPX,2) from ThietLap)
                            and px.MaPX=ThietLap.MaPX and left(right('0'+cast(MaNK as varchar(8)),8),2)=RIGHT(px.MaPX,2)
                            group by left(right('0'+cast(MaNK as varchar(8)),8),2),TenPhuongXa, px.MaPX
                            ) as t1,
                            (
                            select count(MaNK) as SoNu, left(right('0'+cast(MaNK as varchar(8)),8),2) as c2  
                            from NhanKhau
                            where GioiTinh = 0
                            group by left(right('0'+cast(MaNK as varchar(8)),8),2)
                             ) as t2,
                            (
                            select left(right('0'+cast(MaNK as varchar(8)),8),2) as c3, count(MaNK) as Tuoi14  
                            from NhanKhau 
                            where DATEDIFF(yyyy,NgaySinh,GETDATE())>14 
                            group by left(right('0'+cast(MaNK as varchar(8)),8),2)
                            ) as t3
                             where t1.c1 = t2.c2 and t3.c3 = t2.c2
                             ";
            var shk = Common.db.ExecuteStoreQuery<SoHoKhauPublic>(chuoi);
            return shk.ToList();
        }
    }
}
